<?php
namespace app\service;
use think\facade\Db;
use app\model\ReceiptModel;
use app\model\PaymentModel;
use app\model\TransferModel;
use Exception;

class ThreeColService{  
    public function index($params){   
        $where = [];
        $rwhere=[];
        $pwhere=[];
        $twhere=[];
        $fwhere=[];      
        $page = 1;
        $pageCount = 10;        
        $datas=[];
        if(!empty($params)){      
             //按收付款日期查询
             if(!empty($params['start_date']) && !empty($params['end_date'])){              
                if(!empty($params['start_date'])){
                    $rwhere[] = ['receipt_date','>= time',$params['start_date']];  
                    $pwhere[] = ['payment_date','>= time',$params['start_date']];  
                    $twhere[] = ['transfer_date','>= time',$params['start_date']];  
                    $fwhere[] = ['transfer_date','>= time',$params['start_date']];  
                }
                if(!empty($params['end_date'])){
                    $rwhere[] = ['receipt_date','<= time',$params['end_date']];  
                    $pwhere[] = ['payment_date','<= time',$params['end_date']];  
                    $twhere[] = ['transfer_date','<= time',$params['end_date']];  
                    $fwhere[] = ['transfer_date','<= time',$params['end_date']];  
                }
            }     
           
            //页码            
            $page = !empty($params['page']) ? $params['page'] : $page;
            $pageCount = !empty($params['page_count']) ? $params['page_count'] : $pageCount;
           
            //按科目查询
            if(!empty($params['subject_id'])){
                $rwhere[] = ['subject_id','=',$params['subject_id']];    
                $pwhere[] = ['subject_id','=',$params['subject_id']];        
                $twhere[] = ['to_bank','=',$params['subject_id']]; 
                $fwhere[] = ['from_bank','=',$params['subject_id']];        
            }  

            if(!empty($params['invoice_num'])){
                $rwhere[] = ['invoice_num','=',$params['invoice_num']]; 
                $pwhere[] = ['invoice_num','=',$params['invoice_num']];        
                $twhere[] = ['to_number','=',$params['invoice_num']]; 
                $fwhere[] = ['from_number','=',$params['invoice_num']];      
            }
            
        }  
        $Payment = new PaymentModel();
       $Receipt = new ReceiptModel();
        $Transfer = new TransferModel();
        $a = $Receipt->alias('a')
        ->field("a.id,a.type,a.is_review,a.in,a.receipt_date as price_date,a.subject_id,a.money,a.invoice_num,a.remark,b.client_id as x_id,c.start_date,c.sn,c.title")
        ->join('sale_income b','a.income_id=b.id and a.type=b.type')
        ->join('sale c','b.pid=c.id')
        ->where($rwhere)
        ->buildSql();               
        $b = $Receipt->alias('a')
        ->field("a.id,a.type,a.is_review,a.in,a.receipt_date as price_date,a.subject_id,a.money,a.invoice_num,a.remark,b.client_id as x_id,b.start_date,b.sn,b.content as title")
        ->join('single b','a.income_id=b.id and a.type=b.type')
        ->where($rwhere)
        ->buildSql();             
        $c = $Payment->alias('a')
        ->field("a.id,a.type,a.is_review,a.in,a.payment_date as price_date,a.subject_id,a.money,a.invoice_num,a.remark,b.supplier_id as x_id,c.start_date,c.sn,c.title")
        ->join('sale_outlay b','a.outlay_id=b.id and a.type=b.type')
        ->join('sale c','b.pid=c.id')
        ->where($pwhere)        
        ->buildSql();     
        $d = $Payment->alias('a')
        ->field("a.id,a.type,a.is_review,a.in,a.payment_date as price_date,a.subject_id,a.money,a.invoice_num,a.remark,b.supplier_id as x_id,b.start_date,b.sn,b.content as title")
        ->join('single b','a.outlay_id=b.id and a.type=b.type')       
        ->where($pwhere)      
        ->buildSql();  
        $e = $Transfer->alias('a')
        ->field("a.id,a.type,a.is_to_review as is_review,a.to_in,a.transfer_date as price_date,a.to_bank as subject_id,a.to_money as money,a.to_number as invoice_num,a.remark,a.x_id,a.start_date,a.sn,a.title")        
        ->where($twhere)      
        ->buildSql();    

        $f = $Transfer->alias('a')
        ->field("a.id,a.type,a.is_from_review as is_review,a.from_in,a.transfer_date as price_date,a.from_bank as subject_id,a.from_money as money,a.from_number as invoice_num,a.remark,a.x_id,a.start_date,a.sn,a.title")        
        ->where($fwhere)      
        ->buildSql();  
        $e = $Payment->alias('a')
        ->field("a.id,a.type,a.is_review,a.in,a.payment_date as price_date,a.subject_id,a.money,a.invoice_num,a.remark,b.supplier_id as x_id,b.start_date,b.sn,b.content as title")
        ->join('buy b','a.outlay_id=b.id and a.type=b.type')       
        ->where($pwhere)      
        ->buildSql();  
        $datas = Db::table($a.' a')->union([$b,$c,$d,$e,$f,$e])->orderRaw("CAST(invoice_num as decimal)")->select();            
        
        foreach ($datas as &$item) {
            
        }
        
        return ["list"=>$datas,"count"=>0];
    }   

    public function yuer($params){   
        $where = [];
        $rwhere=[];
        $pwhere=[];     
        $datas=[];
        if(!empty($params)){      
             //按收付款日期查询            
            if(!empty($params['start_date'])){
                $rwhere['receipt_date'] = ['<',$params['start_date']];  
                $pwhere['payment_date'] = ['<',$params['start_date']];  
                $cwhere['transfer_date'] = ['<',$params['start_date']];  
                $dwhere['transfer_date'] = ['<',$params['start_date']];  
            }  
            //按科目查询
            if(!empty($params['subject_id'])){
                $rwhere['subject_id'] = $params['subject_id'];      
                $pwhere['subject_id'] = $params['subject_id'];          
                $cwhere['to_bank'] = $params['subject_id'];  
                $dwhere['from_bank'] = $params['subject_id'];        
            }         
        }  
       
        $a = Db::name("Receipt")->where($rwhere)->sum('money');
        $b = Db::name("Payment")->where($pwhere)->sum('money');
        $c = Db::name("transfer")->where($cwhere)->sum('to_money');
        $d = Db::name("transfer")->where($dwhere)->sum('from_money');
        $e = Db::name("bank")->where('id',$params['subject_id'])->value('balance');
        $y=$e+$a+$c-$b-$d;      
       
        return $y;     
    }   
}